﻿CREATE PROCEDURE [dbo].[responses_get_listing]
    @page int = 0,
    @size int = 0,
    @column nvarchar(256),
    @direction int = 0,
    @bookId     int = 0,
    @userId int = 0,
    @dateFrom datetime2 = null,
    @dateTo datetime2  = null
AS
SELECT
        ResponseId,
        Responses.DateOfPublish,
        Responses.BookId,
        Books.Title,
        Users.UserId,
        Users.Email
FROM Responses
    inner join Users ON Responses.UserId = Users.UserId
    inner join Books ON Responses.BookId = Books.BookId
WHERE
    (@bookId=0 OR Responses.BookId=@bookId)
    AND
    (@userId=0 OR Responses.UserId=@userId)
    AND
    (@dateFrom is NULL OR DATEPART(YEAR, @dateFrom) < 1753 OR Responses.DateOfPublish>@dateFrom)
    AND
    (@dateTo is NULL OR DATEPART(YEAR, @dateTo) < 1753 OR Responses.DateOfPublish<@dateTo)
ORDER BY
    CASE WHEN @column = 'UserEmail' AND @direction = 1 THEN Users.Email END DESC,
    CASE WHEN @column = 'UserEmail' AND @direction = 0 THEN Users.Email END ASC,
    CASE WHEN @column = 'BookTitle' AND @direction = 1 THEN Books.Title END DESC,
    CASE WHEN @column = 'BookTitle' AND @direction = 0 THEN Books.Title END ASC,
    CASE WHEN @column = 'DateOfPublish' AND @direction = 1 THEN Responses.DateOfPublish END DESC,
    CASE WHEN @column = 'DateOfPublish' AND @direction = 0 THEN Responses.DateOfPublish END ASC
OFFSET ((@page-1)*@size) ROWS
FETCH NEXT @size ROWS ONLY;

Declare 
    @TotalCount int

SELECT @TotalCount = Count(DISTINCT Responses.ResponseId)
FROM Responses
    inner join Users ON Responses.UserId = Users.UserId
    inner join Books ON Responses.BookId = Books.BookId
WHERE
    (@bookId=0 OR Responses.BookId=@bookId)
    AND
    (@userId=0 OR Responses.UserId=@userId)
    AND
    (@dateFrom is NULL OR DATEPART(YEAR, @dateFrom) < 1753 OR Responses.DateOfPublish>@dateFrom)
    AND
    (@dateTo is NULL OR DATEPART(YEAR, @dateTo) < 1753 OR Responses.DateOfPublish<@dateTo)
RETURN @TotalCount;
